import { Pool, PoolClient } from 'pg'
import bcrypt from 'bcryptjs'

// PostgreSQL连接配置
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432'),
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'postgres',
  database: process.env.DB_NAME || 'aide_db',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
})

export interface Task {
  id: string
  title: string
  description?: string
  due_date?: string
  priority: 'P0' | 'P1' | 'P2' | 'P3'
  category: 'work' | 'personal' | 'learning' | 'health' | 'other'
  tags: string[]
  completed: boolean
  notes?: string
  user_id: string
  status: 'todo' | 'in_progress' | 'review' | 'done'
  board_position: number
  is_recurring?: boolean
  recurring_type?: 'daily' | 'weekly' | 'monthly' | 'yearly'
  recurring_pattern?: string
  parent_recurring_id?: string
  created_at: string
  updated_at: string
}

export interface SubTask {
  id: string
  task_id: string
  title: string
  completed: boolean
}

export function getDatabase() {
  return pool
}

export async function initDatabase(): Promise<void> {
  const client = await pool.connect()
  
  try {
    console.log('🔄 初始化数据库...')

    // 创建用户表
    await client.query(`
      CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        email TEXT UNIQUE,
        phone TEXT UNIQUE,
        password_hash TEXT,
        username TEXT,
        avatar_url TEXT,
        is_verified BOOLEAN DEFAULT FALSE,
        login_type TEXT NOT NULL CHECK (login_type IN ('email', 'phone', 'oauth')),
        oauth_provider TEXT,
        oauth_id TEXT,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        last_login_at TIMESTAMP WITH TIME ZONE,
        
        -- 确保至少有一种登录方式
        CONSTRAINT user_login_method CHECK (
          (email IS NOT NULL AND password_hash IS NOT NULL) OR 
          (phone IS NOT NULL) OR 
          (oauth_provider IS NOT NULL AND oauth_id IS NOT NULL)
        )
      )
    `)
    
    // 创建验证码表
    await client.query(`
      CREATE TABLE IF NOT EXISTS verification_codes (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        type TEXT NOT NULL CHECK (type IN ('phone_login', 'phone_register', 'email_verify', 'password_reset')),
        recipient TEXT NOT NULL, -- 手机号或邮箱
        code TEXT NOT NULL,
        expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
        is_used BOOLEAN DEFAULT FALSE,
        attempts INTEGER DEFAULT 0,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        
        -- 防止重复发送，同一recipient+type在短时间内只能有一个有效验证码
        UNIQUE(recipient, type, expires_at)
      )
    `)
    
    // 创建用户会话表
    await client.query(`
      CREATE TABLE IF NOT EXISTS user_sessions (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        token_hash TEXT NOT NULL,
        device_info TEXT,
        ip_address TEXT,
        expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        last_used_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
      )
    `)

    // 创建任务表 - 完整版本
    await client.query(`
      CREATE TABLE IF NOT EXISTS tasks (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        due_date TEXT, -- 格式: YYYY-MM-DD 或 ISO字符串
        priority TEXT NOT NULL DEFAULT 'P2' CHECK (priority IN ('P0', 'P1', 'P2', 'P3')),
        category TEXT NOT NULL DEFAULT 'other' CHECK (category IN ('work', 'personal', 'learning', 'health', 'other')),
        tags TEXT[] DEFAULT '{}',
        completed BOOLEAN NOT NULL DEFAULT FALSE,
        notes TEXT,
        status TEXT NOT NULL DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
        board_position INTEGER DEFAULT 0,
        is_recurring BOOLEAN DEFAULT FALSE,
        recurring_type TEXT CHECK (recurring_type IN ('daily', 'weekly', 'monthly', 'yearly')),
        recurring_pattern TEXT,
        parent_recurring_id TEXT,
        reminder_minutes INTEGER DEFAULT 30, -- 提前多少分钟提醒，null表示使用全局设置
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (parent_recurring_id) REFERENCES recurring_tasks(id) ON DELETE CASCADE
      )
    `)

    // 创建子任务表
    await client.query(`
      CREATE TABLE IF NOT EXISTS subtasks (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        task_id TEXT NOT NULL,
        title TEXT NOT NULL,
        completed BOOLEAN NOT NULL DEFAULT FALSE,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
      )
    `)

    // 创建重复任务配置表
    await client.query(`
      CREATE TABLE IF NOT EXISTS recurring_tasks (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        title TEXT NOT NULL,
        description TEXT,
        priority TEXT NOT NULL DEFAULT 'P2' CHECK (priority IN ('P0', 'P1', 'P2', 'P3')),
        category TEXT NOT NULL DEFAULT 'other' CHECK (category IN ('work', 'personal', 'learning', 'health', 'other')),
        tags TEXT[] DEFAULT '{}',
        notes TEXT,
        recurring_type TEXT NOT NULL CHECK (recurring_type IN ('daily', 'weekly', 'monthly', 'yearly')),
        recurring_pattern JSONB,
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )
    `)

    // 创建重复任务实例表
    await client.query(`
      CREATE TABLE IF NOT EXISTS recurring_task_instances (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        recurring_task_id TEXT NOT NULL,
        task_id TEXT,
        due_date TEXT NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (recurring_task_id) REFERENCES recurring_tasks (id) ON DELETE CASCADE,
        FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE,
        UNIQUE(recurring_task_id, due_date)
      )
    `)

    // 创建看板列配置表
    await client.query(`
      CREATE TABLE IF NOT EXISTS kanban_columns (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        status TEXT NOT NULL CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
        title TEXT NOT NULL,
        color TEXT NOT NULL DEFAULT '#6B7280',
        column_order INTEGER NOT NULL DEFAULT 0,
        is_visible BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE(user_id, status)
      )
    `)

    // 创建用户行为跟踪表（用于AI个性化建议）
    await client.query(`
      CREATE TABLE IF NOT EXISTS user_behaviors (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        action TEXT NOT NULL,
        entity_type TEXT NOT NULL CHECK (entity_type IN ('task', 'suggestion', 'search', 'filter')),
        entity_id TEXT,
        metadata JSONB,
        timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        
        -- 索引字段
        CONSTRAINT valid_action CHECK (action IN (
          'task_create', 'task_update', 'task_complete', 'task_delete',
          'suggestion_view', 'suggestion_accept', 'suggestion_dismiss', 'suggestion_rate',
          'search_query', 'search_click',
          'filter_apply', 'sort_apply',
          'page_visit', 'feature_use'
        ))
      )
    `)

    // 创建AI建议反馈表
    await client.query(`
      CREATE TABLE IF NOT EXISTS suggestion_feedback (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        suggestion_id TEXT NOT NULL,
        feedback_type TEXT NOT NULL CHECK (feedback_type IN ('accept', 'dismiss', 'snooze')),
        timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )
    `)

    // 创建通知表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notifications (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        title TEXT NOT NULL,
        message TEXT NOT NULL,
        type TEXT NOT NULL CHECK (type IN ('task_reminder', 'task_overdue', 'system', 'achievement')),
        is_read BOOLEAN NOT NULL DEFAULT FALSE,
        task_id TEXT,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
      )
    `)

    // 创建用户设置表
    await client.query(`
      CREATE TABLE IF NOT EXISTS user_settings (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL UNIQUE,
        theme TEXT NOT NULL DEFAULT 'light' CHECK (theme IN ('light', 'dark', 'system')),
        language TEXT NOT NULL DEFAULT 'zh-CN',
        timezone TEXT NOT NULL DEFAULT 'Asia/Shanghai',
        notification_settings JSONB DEFAULT '{}',
        ai_preferences JSONB DEFAULT '{}',
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )
    `)

    // 创建通知设置表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_settings (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL UNIQUE,
        task_reminders BOOLEAN DEFAULT true,
        daily_summary BOOLEAN DEFAULT true,
        weekly_review BOOLEAN DEFAULT true,
        overdue_alerts BOOLEAN DEFAULT true,
        reminder_time TIME DEFAULT '09:00',
        sound_enabled BOOLEAN DEFAULT true,
        vibration_enabled BOOLEAN DEFAULT true,
        email_notifications BOOLEAN DEFAULT false,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      )
    `)

    // 创建通知历史表
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_history (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        type TEXT NOT NULL CHECK (type IN ('reminder', 'overdue', 'summary', 'deadline')),
        title TEXT NOT NULL,
        message TEXT NOT NULL,
        task_id TEXT,
        sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        is_read BOOLEAN DEFAULT false,
        read_at TIMESTAMP WITH TIME ZONE,
        delivery_status TEXT DEFAULT 'sent' CHECK (delivery_status IN ('sent', 'failed', 'pending')),
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE SET NULL
      )
    `)

    // 创建通知队列表（用于延迟发送）
    await client.query(`
      CREATE TABLE IF NOT EXISTS notification_queue (
        id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
        user_id TEXT NOT NULL,
        type TEXT NOT NULL,
        title TEXT NOT NULL,
        message TEXT NOT NULL,
        task_id TEXT,
        scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
        status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed', 'cancelled')),
        attempts INTEGER DEFAULT 0,
        max_attempts INTEGER DEFAULT 3,
        last_attempt_at TIMESTAMP WITH TIME ZONE,
        error_message TEXT,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
      )
    `)

    // 检查并添加缺失的字段（数据库迁移）
    console.log('🔄 检查数据库表结构...')
    
    // 检查tasks表是否缺少字段
    const tasksColumns = await client.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'tasks' AND table_schema = 'public'
    `)
    const existingColumns = tasksColumns.rows.map(row => row.column_name)
    
    // 检查并修复 due_date 字段类型 (TEXT -> TIMESTAMP WITH TIME ZONE)
    const dueDateTypeResult = await client.query(`
      SELECT data_type 
      FROM information_schema.columns 
      WHERE table_name = 'tasks' 
        AND column_name = 'due_date' 
        AND table_schema = 'public'
    `)
    
    if (dueDateTypeResult.rows.length > 0 && dueDateTypeResult.rows[0].data_type === 'text') {
      console.log('🔧 修复due_date字段类型: tasks.due_date (TEXT -> TIMESTAMP WITH TIME ZONE)')
      
      // 只有在环境变量明确要求清除数据时才删除字段
      const shouldClearData = process.env.CLEAR_DATABASE_DATA === 'true'
      
      if (shouldClearData) {
        console.log('🗑️ 环境变量 CLEAR_DATABASE_DATA=true，清空历史数据并修复字段类型')
        await client.query(`ALTER TABLE tasks DROP COLUMN IF EXISTS due_date`)
        await client.query(`
          ALTER TABLE tasks 
          ADD COLUMN due_date TIMESTAMP WITH TIME ZONE
        `)
      } else {
        console.log('ℹ️ 保留现有数据，跳过due_date字段类型修复')
        console.log('💡 如需修复字段类型，请设置环境变量 CLEAR_DATABASE_DATA=true')
      }
    }

    // 添加缺失的字段
    const requiredFields = [
      { name: 'user_id', type: 'TEXT', constraint: '' },
      { name: 'status', type: 'TEXT', constraint: "NOT NULL DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'review', 'done'))" },
      { name: 'board_position', type: 'INTEGER', constraint: 'DEFAULT 0' },
      { name: 'is_recurring', type: 'BOOLEAN', constraint: 'DEFAULT FALSE' },
      { name: 'recurring_type', type: 'TEXT', constraint: "CHECK (recurring_type IN ('daily', 'weekly', 'monthly', 'yearly'))" },
      { name: 'recurring_pattern', type: 'TEXT', constraint: '' },
      { name: 'parent_recurring_id', type: 'TEXT', constraint: '' },
      { name: 'reminder_minutes', type: 'INTEGER', constraint: 'DEFAULT 30' } // 新增：提醒间隔字段
    ]
    
    // 检查并修复时间字段的数据类型
    const timeFields = ['created_at', 'updated_at']
    for (const fieldName of timeFields) {
      if (existingColumns.includes(fieldName)) {
        // 检查字段类型
        const fieldTypeResult = await client.query(`
          SELECT data_type 
          FROM information_schema.columns 
          WHERE table_name = 'tasks' 
            AND column_name = $1 
            AND table_schema = 'public'
        `, [fieldName])
        
        if (fieldTypeResult.rows.length > 0 && fieldTypeResult.rows[0].data_type === 'text') {
          console.log(`🔧 修复时间字段类型: tasks.${fieldName} (TEXT -> TIMESTAMP WITH TIME ZONE)`)
          
          // 先添加新的时间字段
          await client.query(`
            ALTER TABLE tasks 
            ADD COLUMN IF NOT EXISTS ${fieldName}_new TIMESTAMP WITH TIME ZONE
          `)
          
          // 尝试转换现有数据
          await client.query(`
            UPDATE tasks 
            SET ${fieldName}_new = CASE 
              WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}' THEN ${fieldName}::timestamp with time zone
              WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}' THEN ${fieldName}::timestamp with time zone
              WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2}$' THEN (${fieldName} || ' 00:00:00')::timestamp with time zone
              ELSE NOW()
            END
            WHERE ${fieldName}_new IS NULL
          `)
          
          // 删除旧字段并重命名新字段
          await client.query(`ALTER TABLE tasks DROP COLUMN IF EXISTS ${fieldName}`)
          await client.query(`ALTER TABLE tasks RENAME COLUMN ${fieldName}_new TO ${fieldName}`)
          
          // 设置默认值
          if (fieldName === 'created_at') {
            await client.query(`ALTER TABLE tasks ALTER COLUMN ${fieldName} SET DEFAULT NOW()`)
            await client.query(`ALTER TABLE tasks ALTER COLUMN ${fieldName} SET NOT NULL`)
          } else if (fieldName === 'updated_at') {
            await client.query(`ALTER TABLE tasks ALTER COLUMN ${fieldName} SET DEFAULT NOW()`)
            await client.query(`ALTER TABLE tasks ALTER COLUMN ${fieldName} SET NOT NULL`)
          }
        }
      }
    }
    
    // 检查并修复 subtasks 表的时间字段
    const subtasksColumns = await client.query(`
      SELECT column_name, data_type 
      FROM information_schema.columns 
      WHERE table_name = 'subtasks' AND table_schema = 'public'
    `)
    
    for (const row of subtasksColumns.rows) {
      if ((row.column_name === 'created_at' || row.column_name === 'updated_at') && row.data_type === 'text') {
        const fieldName = row.column_name
        console.log(`🔧 修复子任务时间字段类型: subtasks.${fieldName} (TEXT -> TIMESTAMP WITH TIME ZONE)`)
        
        // 先添加新的时间字段
        await client.query(`
          ALTER TABLE subtasks 
          ADD COLUMN IF NOT EXISTS ${fieldName}_new TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        `)
        
        // 尝试转换现有数据
        await client.query(`
          UPDATE subtasks 
          SET ${fieldName}_new = CASE 
            WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}' THEN ${fieldName}::timestamp with time zone
            WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}' THEN ${fieldName}::timestamp with time zone
            WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2}$' THEN (${fieldName} || ' 00:00:00')::timestamp with time zone
            ELSE NOW()
          END
          WHERE ${fieldName}_new IS NULL OR ${fieldName}_new = '1970-01-01 00:00:00+00'::timestamp with time zone
        `)
        
        // 删除旧字段并重命名新字段
        await client.query(`ALTER TABLE subtasks DROP COLUMN IF EXISTS ${fieldName}`)
        await client.query(`ALTER TABLE subtasks RENAME COLUMN ${fieldName}_new TO ${fieldName}`)
        await client.query(`ALTER TABLE subtasks ALTER COLUMN ${fieldName} SET NOT NULL`)
      }
    }
    
    // 检查并修复其他表的时间字段
    const tablesToCheck = [
      { tableName: 'recurring_tasks', timeFields: ['created_at', 'updated_at'] },
      { tableName: 'recurring_task_instances', timeFields: ['created_at'] },
      { tableName: 'users', timeFields: ['created_at', 'updated_at', 'last_login_at'] },
      { tableName: 'verification_codes', timeFields: ['expires_at', 'created_at'] },
      { tableName: 'user_sessions', timeFields: ['expires_at', 'created_at', 'last_used_at'] },
      { tableName: 'kanban_columns', timeFields: ['created_at', 'updated_at'] },
      { tableName: 'user_behaviors', timeFields: ['timestamp'] },
      { tableName: 'suggestion_feedback', timeFields: ['timestamp'] },
      { tableName: 'notifications', timeFields: ['created_at', 'updated_at'] },
      { tableName: 'user_settings', timeFields: ['created_at', 'updated_at'] }
    ]
    
    for (const tableInfo of tablesToCheck) {
      // 检查表是否存在
      const tableExists = await client.query(`
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_name = $1 AND table_schema = 'public'
      `, [tableInfo.tableName])
      
      if (tableExists.rows.length === 0) {
        console.log(`⚠️ 表 ${tableInfo.tableName} 不存在，跳过时间字段检查`)
        continue
      }
      
      // 获取表的列信息
      const tableColumns = await client.query(`
        SELECT column_name, data_type 
        FROM information_schema.columns 
        WHERE table_name = $1 AND table_schema = 'public'
      `, [tableInfo.tableName])
      
      for (const row of tableColumns.rows) {
        if (tableInfo.timeFields.includes(row.column_name) && row.data_type === 'text') {
          const fieldName = row.column_name
          console.log(`🔧 修复时间字段类型: ${tableInfo.tableName}.${fieldName} (TEXT -> TIMESTAMP WITH TIME ZONE)`)
          
          try {
            // 先添加新的时间字段
            await client.query(`
              ALTER TABLE ${tableInfo.tableName} 
              ADD COLUMN IF NOT EXISTS ${fieldName}_new TIMESTAMP WITH TIME ZONE
            `)
            
            // 转换现有数据
            await client.query(`
              UPDATE ${tableInfo.tableName} 
              SET ${fieldName}_new = CASE 
                WHEN ${fieldName} IS NULL OR ${fieldName} = '' THEN 
                  CASE WHEN '${fieldName}' = 'expires_at' THEN NOW() + INTERVAL '1 hour' ELSE NOW() END
                WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}' THEN ${fieldName}::timestamp with time zone
                WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}' THEN ${fieldName}::timestamp with time zone
                WHEN ${fieldName} ~ '^\\d{4}-\\d{2}-\\d{2}$' THEN (${fieldName} || ' 00:00:00')::timestamp with time zone
                ELSE 
                  CASE WHEN '${fieldName}' = 'expires_at' THEN NOW() + INTERVAL '1 hour' ELSE NOW() END
              END
              WHERE ${fieldName}_new IS NULL
            `)
            
            // 删除旧字段并重命名新字段
            await client.query(`ALTER TABLE ${tableInfo.tableName} DROP COLUMN IF EXISTS ${fieldName}`)
            await client.query(`ALTER TABLE ${tableInfo.tableName} RENAME COLUMN ${fieldName}_new TO ${fieldName}`)
            
            // 为某些字段设置NOT NULL约束
            if (['created_at', 'updated_at', 'timestamp'].includes(fieldName)) {
              await client.query(`ALTER TABLE ${tableInfo.tableName} ALTER COLUMN ${fieldName} SET NOT NULL`)
            }
            
            // 设置默认值
            if (fieldName === 'created_at' || fieldName === 'updated_at' || fieldName === 'timestamp') {
              await client.query(`ALTER TABLE ${tableInfo.tableName} ALTER COLUMN ${fieldName} SET DEFAULT NOW()`)
            }
          } catch (error) {
            console.error(`❌ 修复 ${tableInfo.tableName}.${fieldName} 失败:`, error)
          }
        }
      }
    }
    
    for (const field of requiredFields) {
      if (!existingColumns.includes(field.name)) {
        console.log(`🔧 添加缺失字段: tasks.${field.name}`)
        await client.query(`
          ALTER TABLE tasks 
          ADD COLUMN IF NOT EXISTS ${field.name} ${field.type} ${field.constraint}
        `)
      }
    }
    
    // 检查并添加外键约束
    try {
      await client.query(`
        ALTER TABLE tasks 
        ADD CONSTRAINT IF NOT EXISTS fk_tasks_user_id 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      `)
      
      await client.query(`
        ALTER TABLE tasks 
        ADD CONSTRAINT IF NOT EXISTS fk_tasks_parent_recurring_id 
        FOREIGN KEY (parent_recurring_id) REFERENCES tasks(id) ON DELETE CASCADE
      `)
    } catch (error) {
      // 忽略约束已存在的错误
      console.log('⚠️ 外键约束可能已存在，跳过')
    }

    // 创建索引以提高查询性能
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_completed ON tasks(completed)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_priority ON tasks(priority)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_category ON tasks(category)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_is_recurring ON tasks(is_recurring)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_tasks_parent_recurring_id ON tasks(parent_recurring_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_subtasks_task_id ON subtasks(task_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_recurring_tasks_user_id ON recurring_tasks(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_recurring_task_instances_recurring_task_id ON recurring_task_instances(recurring_task_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_kanban_columns_user_id ON kanban_columns(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_user_behaviors_user_id ON user_behaviors(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_user_behaviors_timestamp ON user_behaviors(timestamp DESC)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON notifications(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notifications_is_read ON notifications(is_read)`)
    
    // 通知系统表索引
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_settings_user_id ON notification_settings(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_history_user_id ON notification_history(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_history_sent_at ON notification_history(sent_at DESC)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_history_type ON notification_history(type)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_history_task_id ON notification_history(task_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_queue_user_id ON notification_queue(user_id)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_queue_scheduled_at ON notification_queue(scheduled_at)`)
    await client.query(`CREATE INDEX IF NOT EXISTS idx_notification_queue_status ON notification_queue(status)`)

    // 数据清理控制（通过环境变量控制）
    const clearDataConfig = process.env.CLEAR_DATABASE_DATA
    const shouldClearData = clearDataConfig === 'true' || clearDataConfig === '1'
    
    if (shouldClearData) {
      console.log('🗑️ 环境变量 CLEAR_DATABASE_DATA=' + clearDataConfig + '，清空历史数据以确保数据类型一致性')
      console.log('⚠️ 警告：这将删除所有现有数据！')
      
      await client.query('TRUNCATE TABLE subtasks CASCADE')
      await client.query('TRUNCATE TABLE tasks CASCADE')
      await client.query('TRUNCATE TABLE recurring_task_instances CASCADE')
      await client.query('TRUNCATE TABLE recurring_tasks CASCADE')
      await client.query('TRUNCATE TABLE user_behaviors CASCADE')
      await client.query('TRUNCATE TABLE suggestion_feedback CASCADE')
      await client.query('TRUNCATE TABLE notifications CASCADE')
      await client.query('TRUNCATE TABLE notification_history CASCADE')
      await client.query('TRUNCATE TABLE notification_queue CASCADE')
      await client.query('TRUNCATE TABLE notification_settings CASCADE')
      console.log('✅ 数据清除完成')
    } else {
      console.log('ℹ️ 保留现有数据')
      console.log('💡 如需清除数据，请设置环境变量：')
      console.log('   - Windows: $env:CLEAR_DATABASE_DATA="true"')
      console.log('   - Linux/Mac: export CLEAR_DATABASE_DATA=true')
      console.log('   - Docker: CLEAR_DATABASE_DATA=true')
    }
    
    // 创建默认管理员用户（如果不存在）
    let adminUserId: string
    const adminExists = await client.query(`
      SELECT id FROM users WHERE email = 'admin@aide.com'
    `)
    
    if (adminExists.rows.length === 0) {
      const hashedPassword = await bcrypt.hash('admin123456', 12)
      
      const adminResult = await client.query(`
        INSERT INTO users (email, password_hash, username, login_type, is_verified)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING id
      `, ['admin@aide.com', hashedPassword, '管理员', 'email', true])
      
      adminUserId = adminResult.rows[0].id
      console.log('✅ 创建默认管理员用户: admin@aide.com / admin123456')
    } else {
      adminUserId = adminExists.rows[0].id
      console.log('ℹ️ 管理员用户已存在')
    }

    // 为现有用户创建默认通知设置
    const existingUsersResult = await client.query(`
      SELECT id FROM users 
      WHERE id NOT IN (SELECT user_id FROM notification_settings WHERE user_id IS NOT NULL)
    `)

    if (existingUsersResult.rows.length > 0) {
      console.log(`📝 为 ${existingUsersResult.rows.length} 个现有用户创建默认通知设置...`)
      
      for (const user of existingUsersResult.rows) {
        await client.query(`
          INSERT INTO notification_settings (
            user_id, task_reminders, daily_summary, weekly_review, 
            overdue_alerts, reminder_time, sound_enabled, 
            vibration_enabled, email_notifications
          ) VALUES ($1, true, true, true, true, '09:00', true, true, false)
        `, [user.id])
      }
      
      console.log('✅ 默认通知设置创建完成')
    } else {
      console.log('ℹ️ 所有用户都已有通知设置')
    }

    // 检查是否需要插入示例数据
    const existingTasks = await client.query('SELECT COUNT(*) FROM tasks')
    if (parseInt(existingTasks.rows[0].count) === 0) {
      console.log('📝 插入示例数据...')
      
      // 使用管理员用户ID创建示例任务
      const sampleTasks = [
        {
          id: '1',
          title: '完成项目文档',
          description: '编写项目的技术文档和用户手册',
          due_date: '2024-02-15',
          priority: 'P1',
          category: 'work',
          tags: ['文档', '项目'],
          completed: false,
          notes: '需要包含API文档和部署说明',
          user_id: adminUserId,
          status: 'in_progress',
          board_position: 1,
          is_recurring: false,
          created_at: new Date().toISOString(),
          updated_at: new Date().toISOString()
        },
        {
          id: '2',
          title: '学习新技术',
          description: '学习React 18的新特性',
          due_date: '2024-02-20',
          priority: 'P2',
          category: 'learning',
          tags: ['技术', '学习'],
          completed: false,
          notes: '重点关注并发特性和Suspense',
          user_id: adminUserId,
          status: 'todo',
          board_position: 0,
          is_recurring: true,
          recurring_type: 'weekly',
          recurring_pattern: '{"dayOfWeek": 1}',
          created_at: new Date().toISOString(),
          updated_at: new Date().toISOString()
        }
      ]

      for (const task of sampleTasks) {
        await client.query(`
          INSERT INTO tasks (id, title, description, due_date, priority, category, tags, completed, notes, user_id, status, board_position, is_recurring, recurring_type, recurring_pattern, created_at, updated_at)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
        `, [
          task.id, task.title, task.description, task.due_date, task.priority, 
          task.category, task.tags, task.completed, task.notes, task.user_id,
          task.status, task.board_position, task.is_recurring, task.recurring_type,
          task.recurring_pattern, task.created_at, task.updated_at
        ])
      }

      // 插入示例子任务
      const sampleSubtasks = [
        { id: 'sub-1', task_id: '1', title: '编写API文档', completed: false },
        { id: 'sub-2', task_id: '1', title: '编写部署说明', completed: true },
        { id: 'sub-3', task_id: '2', title: '阅读官方文档', completed: false }
      ]

      for (const subtask of sampleSubtasks) {
        await client.query(`
          INSERT INTO subtasks (id, task_id, title, completed)
          VALUES ($1, $2, $3, $4)
        `, [subtask.id, subtask.task_id, subtask.title, subtask.completed])
      }

      console.log('✅ 示例数据插入完成')
    } else {
      console.log('ℹ️ 任务数据已存在，跳过示例数据插入')
    }

    console.log('✅ PostgreSQL数据库初始化完成')
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error)
    throw error
  } finally {
    client.release()
  }
}